package com.apestech.framework.sql.condition;

import java.util.ArrayList;

import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.sql.MultiDataBase;
import com.apestech.framework.sql.modul.*;
import com.apestech.framework.sql.variable.SqlSection;
import com.apestech.framework.util.callback.SpiltStrCallback;
import com.apestech.framework.xml.sql.modul.QueryRule;
import com.apestech.framework.xml.sql.SqlNodeVar;
import com.apestech.framework.util.DateUtil;
import com.apestech.framework.util.StringUtil;

public class ConditionSql extends StringUtil{

	public class SubSql{
		private String sql;
		private Object value;
		public SubSql(String sql, Object value){
			this.sql=sql;
			this.value=value;
		}
		public String getSql() {
			return sql;
		}
		public Object getValue() {
			return value;
		}
	}

	private ConditionMap param;
	private FromList fromtab;
	private SqlList sqlparam;
	private String field;
	private String sql;
	private MultiDataBase db;
	private boolean sub=false;
	
	public ConditionSql(ConditionMap param,FromList fromtab) throws Exception{
		this.param=param;
		this.fromtab=fromtab;
		this.sqlparam=new SqlList();
		this.db=new MultiDataBase(this.param.getDbType());
	}
	
	public ConditionMap expain() throws Exception{
		field="";
		int len=fromtab.size();
		for(int i=0; i<len; i++){
			String tab=fromtab.getTable(i);
			String alias=fromtab.getAlias(i);
			setTableCondition(tab, alias, i>0);
		}
		return this.param;
	}

	public void setTableCondition(String tableName, String alias,boolean max) throws Exception {
		Table tab=fromtab.getDictTable(tableName);
		String fields=tab.getColumn();
		String dateCol=tab.getDateColumn();
		ArrayList<String> klist=param.getKeylist();
		int len=klist.size();
		for(int i=0; i<len; i++){
			boolean flag=false;
			String key=klist.get(i);
			String k=key;
			if(key.indexOf(".")>=0){
				k=key.split("\\.")[0];
			}
			if((","+fields+",").indexOf(","+k+",")>=0){
				if((","+field+",").indexOf(","+key+",")<0){
					addParam(tableName, alias, key,
							(dateCol!=null && (","+dateCol+",").indexOf(","+key+",")>=0));
					field=field+","+key;
					flag=true;
				}
			}
			if(param.getRule().getExpFieldById(key)!=null && !flag){
				addParam(tableName, alias, key, false);
				field=field+","+key;
			}
		}
		if(!param.isAlldata() && !max && !sub){
			addSubSql(db.getMaxCountWhere(param.isXls()), null);
		}
	}

    public void addParam(String tableName, String alias, String key, boolean isdate) throws Exception {
		ArrayList<FieldCondition> flist=param.getField(key);
		for(FieldCondition f : flist){
			QueryRule.Exp field=null;
			if("date".equals(f.getType())) isdate=true;
			if("EQ".equals(f.getOperator()) || "IN".equals(f.getOperator())){
				field=param.getRule().getExpFieldById(key);
			}
			if(field==null){
				String alias1=param.getRule().getSameas().getString(key);
				boolean isdate1=isdate;
				if(alias1!=null){
					execSplitStr(alias1, ",", new SpiltStrCallback() {
						@Override
						public void exec(String id) throws Exception {
							Table table=fromtab.getDictTable(fromtab.getTableFromAlias(id));  //, tree, treeid, treepid
							SubSql s=getSubSql(key, f.getValue(), f.getOperator(), id, isdate1, table, f.getTree(), f.getId(), f.getParentid());
							if(!"".equals(s.getSql().trim())){
								addSubSql(" AND "+s.getSql()+" ", s.getValue());
							}
						}
					});
				} else {
					Table table=fromtab.getDictTable(fromtab.getTableFromAlias(alias));  //, tree, treeid, treepid
					SubSql s=getSubSql(key, f.getValue(), f.getOperator(), alias, isdate, table, f.getTree(), f.getId(), f.getParentid());
					if(!"".equals(s.getSql().trim())){
						addSubSql(" AND "+s.getSql()+" ", s.getValue());
					}
				}
			} else {
				boolean flag=false;
				ArrayList<QueryRule.ExpField> fields=field.getFields();
				int len=fields.size();{
					for(int i=0; i<len; i++){
						if(alias.equals(fields.get(i).getAlias())){
							flag=true;
						} else if(!flag){
							return;
						}
					}
				}

				addSubSql(" "+field.getOuterExp()+" (", null);
				int j=0;
				for(int i=0; i<len; i++){
					Table table=fromtab.getDictTable(fromtab.getTableFromAlias(alias));
					String fkey=fields.get(i).getField();
					if(fkey==null) fkey=key;
					String oper=fields.get(i).getExpress();
					if(SqlNodeVar.getInnerValue().equals(f.getValue())){
						oper=null;
					} else if(oper==null){
						oper="EQ";
					}
					SubSql s=getSubSql(fkey, f.getValue(), oper, fields.get(i).getAlias(), isdate, table, f.getTree(), f.getId(), f.getParentid());
					String sql1="";
					String sql=s.getSql();
					if(fields.get(i).getSql()!=null && alias.equals(fields.get(i).getAlias())){
						String s1=fields.get(i).getSql();
						if(table.getTreeId(fkey)!=null){
							s1=" EXISTS ("+
									db.preTree(table.getTreeTable(fkey), table.getTreeId(fkey), fkey, fields.get(i).getAlias(), f.getValue())+
									s1+db.sufTree(table.getTreeId(fkey), table.getTreePId(fkey))+")";
						}
						if("".equals(sql)) sql=s1;
						else sql="("+sql+" AND "+s1+")";
					}
					if(!"".equals(sql.trim())){
						if(j==0) sql1=" "+sql+" ";
						else sql1=" "+field.getInnerExp()+" "+sql+" ";
						addSubSql(sql1, s.getValue());
						j++;
					}
				}
				addSubSql(") ", null);
			}
		}
	}

	private SubSql getSubSql(String key, Object value, String oper, String alias, boolean isdate, Table table, String tree, String treeid, String treepid) throws Exception {
		if(oper==null) return new SubSql("", value);
		if(isdate){
			if(value instanceof  String){
				if(!"SYSDATE".equals(value)){
					value=DateUtil.strToDate((String) value).getTime();
				}
			}
		}
		if(value instanceof ArrayList){
			if(!"IN".equals(oper) && !"NIN".equals(oper) && !"TREE".equals(oper) && !"PARENT".equals(oper)){
				oper="IN";
			}
		}
		String sql="";
		QueryExpression query=null;
		if(key.indexOf(".")>=0){
			MultiDataBase db=new MultiDataBase(fromtab.getDbType());
			MultiDB.MutiField f=db.getMutiFieldWhere(fromtab, key, alias);
			String datatype=f.getTable().getTypeName(f.getField());
			switch (datatype){
				case "NUMBER":{
					if(f.getTable().getSize(f.getField())==0) value=castToLong(value);
					else value=castToDate(value);
					break;
				}
				case "VARCHAR2":{
					value=castToString(value);
					break;
				}
				case "DATE":{
					value=castToDate(value);
					break;
				}
			}
			String s=getQuerySql(key, value, oper, f.getAlais(), f.getTable(), tree, treeid, treepid)+f.getSql2();
			if(!"".equals(s.trim())){
				sql=f.getSql1()+" AND "+s;
			}
		} else  sql=getQuerySql(key, value, oper, alias, table, tree, treeid, treepid);
		return new SubSql(sql, value);
	}

	private String getQuerySql(String key, Object value, String oper, String alias, Table table, String tree, String treeid, String treepid) throws Exception {
		QueryExpression query=new QueryExpression(key, value, oper, alias, param.getDbType());
		if(key.indexOf(".")>=0) {
			String[] list=key.split("\\.");
			key=list[list.length-2];
		}
		if(table.getTreeTable(key)!=null){
			query.setTree(table.getTreeTable(key), table.getTreeId(key), table.getTreePId(key));
		} else  if(tree!=null){
		    query.setTree(tree, treeid, treepid);
        }
		query.getCondition();
		return query.getSql();
	}

	private void addSubSql(String s, Object value) throws Exception {
		if(!"".equals(s)){
			SqlSection sqlvar=new SqlSection(fromtab, value);
			sqlvar.doVar(s);
			this.sqlparam.add(sqlvar.getParam());
			if(sql==null) sql="";
			sql=sql+s;
		}
	}

	public SqlList getSqlparam() {
		return sqlparam;
	}

	public String getSql() {
		return sql;
	}


	public void setSub(boolean sub) {
		this.sub = sub;
	}
}
